from flask import Flask, jsonify, request, send_file, Response
from datetime import datetime
import sqlite3
import os
import numpy as np
import volleyball_detection
from volleyball_detection import process_video
import cv2
import threading

app = Flask(__name__)

@app.route('/api/camera/<filename>')
def camera_feed(filename):
    volleyball_detection.counter_enable = True
    video_path = os.path.join('uploads', f"{filename}.mp4")
    # video_path = "uploads/527_1742982375.mp4"
    return send_file(
            video_path,
            mimetype='video/mp4',
            as_attachment=False,
            conditional=True
    )

# 数据库初始化
def init_db():
    if not os.path.exists('volleyball.db'):
        conn = sqlite3.connect('volleyball.db')
        c = conn.cursor()
        
        # 创建学生基础信息表
        c.execute('''
            CREATE TABLE IF NOT EXISTS student_info (
                student_id VARCHAR2(32)  PRIMARY KEY,
                name VARCHAR2(128) NOT NULL,
                gender VARCHAR2(32)   NOT NULL
            )
        ''')
        
        # 创建测试记录表
        c.execute('''
            CREATE TABLE IF NOT EXISTS test_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name       VARCHAR2(128)      not null,
                gender     VARCHAR2(32)       not null,
                student_id VARCHAR2(32)       not null,
                score INTEGER default 0 not null,
                date datatime default current_timestamp,
                video_path VARCHAR2(512),
                counter_data TEXT,
                FOREIGN KEY (student_id) REFERENCES student_info(student_id)
            )
        ''')

        # 创建系统设置表
        c.execute('''
            CREATE TABLE IF NOT EXISTS system_settings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                top REAL DEFAULT 0.0,
                left REAL DEFAULT 0.0,
                bottom REAL DEFAULT 0.0,
                right REAL DEFAULT 0.0,
                ground_top REAL DEFAULT 580.0,
                ground_left REAL DEFAULT 0.0,
                ground_bottom REAL DEFAULT 0.0,
                ground_right REAL DEFAULT 0.0
            )
        ''')

        # 插入默认系统设置
        c.execute('''
            INSERT INTO system_settings (id, top, left, bottom, right, ground_top, ground_left, ground_bottom, ground_right)
            VALUES (1, 0.0, 0.0, 0.0, 0.0, 580.0, 0.0, 0.0, 0.0)
        ''')

        conn.commit()
        conn.close()

def add_test_record(data):
        # 验证必要字段
        required_fields = ['name', 'student_id', 'gender']
        for field in required_fields:
            if field not in data:
                return jsonify({'error': f'缺少必要字段: {field}'}), 400
        
        score = data['score'] or 0
        if 'score' not in data:
            data['score'] = 0
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M')
        
        # 连接数据库并插入记录
        conn = sqlite3.connect('volleyball.db')
        c = conn.cursor()
        c.execute('''
            INSERT INTO test_records (name, student_id, gender, score, date, video_path)
            VALUES (?, ?, ?, ?, ?, ?)
        ''', (
            data['name'],
            data['student_id'],
            data['gender'],
            data['score'],  # 默认分数为0
            current_time,
            data.get('video_path', '')  # 可选字段
        ))
        conn.commit()
        record_id = c.lastrowid
        conn.close()
        return record_id

# 添加学生信息
@app.route('/api/student_info', methods=['POST'])
def add_student_info():
    try:
        data = request.get_json()
        
        # 验证必要字段
        required_fields = ['name', 'student_id', 'gender']
        for field in required_fields:
            if field not in data:
                return jsonify({'error': f'缺少必要字段: {field}'}), 400
        
        # 连接数据库并插入记录
        conn = sqlite3.connect('volleyball.db')
        c = conn.cursor()
        
        # 检查学号是否已存在
        c.execute('SELECT student_id FROM student_info WHERE student_id = ?', (data['student_id'],))
        if c.fetchone():
            conn.close()
            return jsonify({'error': '该学号已存在'}), 400
        
        # 插入新学生信息
        c.execute('''
            INSERT INTO student_info (student_id, name, gender)
            VALUES (?, ?, ?)
        ''', (
            data['student_id'],
            data['name'],
            data['gender']
        ))
        conn.commit()
        conn.close()
        
        return jsonify({
            'message': '学生信息添加成功'
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# 获取学生信息列表
@app.route('/api/student_info', methods=['GET'])
def get_student_info():
    try:
        conn = sqlite3.connect('volleyball.db')
        c = conn.cursor()
        c.execute('SELECT student_id, name, gender FROM student_info ORDER BY student_id')
        students = c.fetchall()
        conn.close()

        # 格式化学生数据
        formatted_students = [
            {
                'student_id': student[0],
                'name': student[1],
                'gender': student[2]
            }
            for student in students
        ]

        return jsonify(formatted_students)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# 获取测试记录列表
@app.route('/api/test_records', methods=['GET'])
def get_test_records():
    try:
        conn = sqlite3.connect('volleyball.db')
        c = conn.cursor()
        c.execute('''
            SELECT r.id, s.name, r.score, r.date, r.video_path 
            FROM test_records r 
            JOIN student_info s ON r.student_id = s.student_id 
            ORDER BY r.date DESC
        ''')
        records = c.fetchall()
        conn.close()

        # 格式化记录数据
        formatted_records = [
            {
                'id': record[0],
                'name': record[1],
                'score': record[2],
                'date': record[3],
                'video_path': record[4]
            }
            for record in records
        ]

        return jsonify(formatted_records)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# 提交测试记录
@app.route('/api/test_records', methods=['POST'])
def submit_test_record():
    try:
        data = request.get_json()
        record_id = add_test_record(data)
        return jsonify({
            'message': '记录提交成功',
            'record_id': record_id
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# 获取测试记录的视频流
@app.route('/api/test_record/<int:record_id>/video', methods=['GET'])
def get_test_record_video(record_id):
    try:
        conn = sqlite3.connect('volleyball.db')
        c = conn.cursor()
        c.execute('SELECT video_path FROM test_records WHERE id = ?', (record_id,))
        record = c.fetchone()
        conn.close()

        if not record or not record[0]:
            return jsonify({'error': '未找到视频文件'}), 404

        video_path = record[0]
        if not os.path.exists(video_path):
            return jsonify({'error': '视频文件不存在'}), 404
        
        return send_file(
            video_path,
            mimetype='video/mp4',
            as_attachment=False,
            conditional=True
        )
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# 根据学号查询学生信息
@app.route('/api/student_info/<student_id>', methods=['GET'])
def get_student_by_id(student_id):
    try:
        conn = sqlite3.connect('volleyball.db')
        c = conn.cursor()
        c.execute('SELECT student_id, name, gender FROM student_info WHERE student_id = ?', (student_id,))
        student = c.fetchone()
        conn.close()

        if not student:
            return jsonify({'error': '未找到该学号的学生信息'}), 404

        return jsonify({
            'student_id': student[0],
            'name': student[1],
            'gender': student[2]
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# 根据记录ID获取测试记录
@app.route('/api/test_record/<int:record_id>', methods=['GET'])
def get_test_record(record_id):
    try:
        conn = sqlite3.connect('volleyball.db')
        c = conn.cursor()
        c.execute('''
            SELECT r.id, s.name, s.student_id, s.gender, r.score, r.date, r.video_path 
            FROM test_records r 
            JOIN student_info s ON r.student_id = s.student_id 
            WHERE r.id = ?
        ''', (record_id,))
        record = c.fetchone()
        conn.close()

        if not record:
            return jsonify({'error': '未找到该测试记录'}), 404

        return jsonify({
            'id': record[0],
            'name': record[1],
            'student_id': record[2],
            'gender': record[3],
            'score': record[4],
            'date': record[5],
            'video_path': record[6]
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/disable_counter', methods=['POST'])
def disable_counter():
    try:
        # 将counter_enable设置为False
        volleyball_detection.counter_enable = False
        return jsonify({'status': 'success'})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/test_record/<int:record_id>', methods=['DELETE'])
def delete_test_record(record_id):
    try:
        conn = sqlite3.connect('volleyball.db')
        c = conn.cursor()
        
        # 获取视频路径
        c.execute('SELECT video_path FROM test_records WHERE id = ?', (record_id,))
        result = c.fetchone()
        
        if result:
            video_path = result[0]
            # 删除视频文件
            if video_path and os.path.exists(video_path):
                os.remove(video_path)
            
            # 删除数据库记录
            c.execute('DELETE FROM test_records WHERE id = ?', (record_id,))
            conn.commit()
            return jsonify({'status': 'success'})
        else:
            return jsonify({'error': '记录不存在'}), 404
            
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    finally:
        conn.close()

@app.route('/api/save_settings', methods=['POST'])
def save_settings():
    try:
        data = request.get_json()
        detection_area = data.get('detection_area', {})
        ground_area = data.get('ground_area', {})
        
        conn = sqlite3.connect('volleyball.db')
        cursor = conn.cursor()
        
        # 更新系统设置表中的检测区域和地面区域设置
        cursor.execute('''
            UPDATE system_settings 
            SET top = ?, left = ?, bottom = ?, right = ?,
                ground_top = ?, ground_left = ?, ground_bottom = ?, ground_right = ?
            WHERE id = 1
        ''', (
            detection_area.get('top', 0),
            detection_area.get('left', 0),
            detection_area.get('bottom', 0),
            detection_area.get('right', 0),
            ground_area.get('top', 580),
            ground_area.get('left', 0),
            ground_area.get('bottom', 0),
            ground_area.get('right', 0)
        ))
        
        conn.commit()
        conn.close()
        
        return jsonify({'message': '设置已保存'})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

if __name__ == '__main__':
    init_db()
    
    
    # 启动Flask服务器
    app.run(debug=True, port=5000) 